﻿Imports System.Data.OleDb
Imports System.Data.SqlClient

Public Class SQLProvider
    Implements IDisposable

    Private m_strConnection As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\QuanLiHocSinh.mdb;User Id=;Password=;"
    Private m_connection As OleDbConnection

    Public Sub Open()
        If m_connection Is Nothing Then
            m_connection = New OleDbConnection(m_strConnection)
        End If
        m_connection.Open()

    End Sub

    Public Sub Close()
        m_connection.Close()
    End Sub

    Public Function ExecNoneQuery(ByVal strSql As String) As Integer
        ' Mo ket noi
        Open()
        ' Thuc thi cau truy van
        Dim command As OleDbCommand = New OleDbCommand(strSql, m_connection)
        Dim intR = command.ExecuteNonQuery()

        ' Dong ket noi
        Close()
        Return intR
    End Function

    Public Function FillDataTable(ByVal strSql As String) As DataTable
        ' Mo ket noi
        Open()
        ' Thuc thi cau truy van
        Dim adapter As OleDbDataAdapter = New OleDbDataAdapter(strSql, m_connection)
        Dim dt As DataTable = New DataTable
        adapter.Fill(dt)
        ' Doc du lieu
        Close()
        Return dt
        ' Dong ket noi
    End Function

    Public Function ExecuteReader(ByVal strSql As String) As IDataReader
        Try
            Open()
            ' Thuc thi cau truy van
            Dim command As OleDbCommand = New OleDbCommand(strSql, m_connection)
            Return command.ExecuteReader()
        Catch ex As Exception
            Close()
            Throw New Exception(ex.Message)
        End Try

    End Function

    Public Sub Dispose() Implements IDisposable.Dispose
        If m_connection.State = ConnectionState.Open Then
            m_connection.Close()
            m_connection.Dispose()
        End If
        GC.SuppressFinalize(Me)
    End Sub

    Public Shared Function ConnectDB(ByVal dbName As String) As OleDbConnection
        Dim strCon = "Provider = Microsoft.Jet.OLEDB.4.0; Data Source = " & dbName
        Dim conn As New OleDbConnection(strCon)
        conn.Open()
        Return conn
    End Function
End Class
